package com.wy.util.HRBU;

import com.wy.util.HRBU.po.TCoursebksxkxs;

import java.io.File;
import java.io.FileWriter;
import java.io.IOException;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * Created by wy on 2018/2/27.
 * 哈工程课表获取
 *
 */
public class GetCourse2 implements Runnable{

    private int startNum;

    public GetCourse2(int startNum){
        this.startNum = startNum;
    }

    private String defaultSkzc0 = "000000000000000000000000000000";//默认上课周次


    public static String sqlCount = "SELECT count(*) as count FROM \"JWXT\".\"V_XS_KB_APP\" where ZXJXJHH = '2017-2018-2'";
    public static String sql = "SELECT * FROM  (  SELECT A.*, ROWNUM RN  FROM (SELECT * FROM \"JWXT\".\"V_XS_KB_APP\" where ZXJXJHH = '2017-2018-2') A  WHERE ROWNUM <= ?  )  WHERE RN >= ? ";

    /**
     *
     * @param conn 数据库连接
     * @param startRowNum 查询数据库时的rownum的最小值，最大值为startRowNum + 50000
     */
    public void execute(Connection conn, int startRowNum){
        PreparedStatement ps = null;
        ResultSet rs = null;
        int totalPage;
        FileWriter fw = null;
        File file=new File("哈工程课程2.sql");
        try {
            fw = new FileWriter(file, true);
            StringBuilder insertSql = new StringBuilder("INSERT INTO `mobilecampus`.`t_coursebksxkxs` (`zxjxjhh`, `xh`, `xm`, `kxh`, `kch`, `kcmc`, `skzc`, `skxq`, `skjc`, `cxjc`, `jxlh`, `jxlm`, `jsh`, `jsm`, `schoolid`) VALUES (\n ");
            StringBuilder temp = new StringBuilder();
            ps = conn.prepareStatement(sqlCount);
            rs = ps.executeQuery();
            rs.next();
            totalPage = rs.getInt("count")/5000 + 1;
            ps.close();
            rs.close();
            for (int page = 0; page < totalPage; page++){
                System.out.println("执行第" + page + "页@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@");
                ps = conn.prepareStatement(sql);
                ps.setInt(1, (page + 1) * 5000);
                ps.setInt(2, page  * 5000);
                rs = ps.executeQuery();
                while (rs.next()){

                    temp.append(insertSql + "2017-2018-1-2," +  rs.getString("Xh") + "," + rs.getString("XM") + "," + rs.getString("KXH") + "," + rs.getString("KCH")
                    +"," + rs.getString("KCMC") + "," + revertSkzc(rs.getString("SKZC")).toString() + "," + (rs.getString("SKJC1").charAt(0) - '0') + "," + (rs.getString("SKJC1").charAt(2) - '0')
                    + "," + ((rs.getString("SKJC1").length() - 1) / 2) + "," + rs.getString("JZWH") + "," + rs.getString("JSH") + "," + rs.getString("JSM") + "," + 193);
                    temp.append(")\n");
                }
                ps.close();
                rs.close();
            }
        fw.flush();
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            try {
                fw.close();
                rs.close();
                ps.close();
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }
    //转换上课周次
    public static StringBuilder revertSkzc(String skzc){
        System.out.println(skzc);
        StringBuilder defaultSkzc = new StringBuilder("000000000000000000000000000000");//默认上课周次
        String[] a = skzc.split(",");
        for (int i = 0; i < a.length; i++){
            if (!a[i].contains("-")){
                defaultSkzc.replace(Integer.valueOf(a[i]) - 1, Integer.valueOf(a[i]), "1");
                continue;
            }
            String[] b = a[i].split("-");
            for (int j = Integer.valueOf(b[0]); j <= Integer.valueOf(b[1]); j++){
                defaultSkzc.replace(j - 1, j, "1");

            }
        }
        return defaultSkzc;
    }

    public StringBuilder sqlProducer(TCoursebksxkxs tc){
        StringBuilder insertSql = new StringBuilder("INSERT INTO `mobilecampus`.`t_coursebksxkxs` (`zxjxjhh`, `xh`, `xm`, `kxh`, `kch`, `kcmc`, `skzc`, `skxq`, `skjc`, `cxjc`, `jxlh`, `jxlm`, `jsh`, `jsm`, `schoolid`) VALUES (\n ");
        insertSql.append(tc.getZxjxjhh() + "," + tc.getXh() + "," + tc.getXm() + "," + tc.getKxh() + "," + tc.getKch() + "," + tc.getKcmc() + "," + tc.getSkzc() + ","+ tc.getSkxq() + "," + tc.getSkjc() + "," + tc.getCxjc() + "," + tc.getJxlh() + "," + tc.getJxlm() + "," + tc.getJsh() + "," + tc.getJsm() + "," + tc.getSchoolid());
        insertSql.append(");\n");
        return insertSql;
    }

    public static Connection getConn(){
        Connection conn = null;
        //String className = "com.mysql.jdbc.Driver";
        String className = "oracle.jdbc.driver.OracleDriver";
        String url ="jdbc:oracle:thin:@//192.168.9.152:1521/ORCL";
        String username ="appxt";
        String password ="appxt_20171013";
        try {
            Class.forName(className);
            try {
                conn = DriverManager.getConnection(url, username, password);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
        return conn;
    }



    @Override
    public void run() {
        execute(getConn(), startNum);
    }



}